﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// DB_relative 的摘要描述
/// </summary>
public static class DB_relative
{
    /// <summary>
    /// 取得親屬資料
    /// </summary>
    /// <returns></returns>
    public static DataTable SelectRelativeList(string EmployeeId)
    {
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter("select * from TravelRelative where EmployeeId=@EmployeeId", Common.DBConnectionString);
        da.SelectCommand.Parameters.AddWithValue("@EmployeeId", EmployeeId);
        da.Fill(dt);
        return dt;
    }
    public static bool ChenkIdExist(string Rid)
    {
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter("select * from TravelRelative where Rid=@Rid", Common.DBConnectionString);
        da.SelectCommand.Parameters.AddWithValue("@Rid", Rid);
        da.Fill(dt);
        if (dt != null)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    /// <summary>
    /// 新增親屬資料
    /// </summary>
    /// <returns></returns>
    public static void InsterTable(string Rid, string EmployeeId, string Rname, string Rbirthday, string RFood)
    {
        SqlConnection cn = new SqlConnection(Common.DBConnectionString);

        SqlCommand cmd = new SqlCommand(
             "INSERT INTO TravelRelative (Rid, EmployeeId,Rname,Rbirthday,RFood,UpdateTime) VALUES (@Rid, @EmployeeId,@Rname,@Rbirthday,@RFood,@UpdateTime)",
             cn);
        cmd.Parameters.AddWithValue("@Rid", Rid);
        cmd.Parameters.AddWithValue("@EmployeeId", EmployeeId);
        cmd.Parameters.AddWithValue("@Rname", Rname);
        cmd.Parameters.AddWithValue("@Rbirthday", Rbirthday);
        cmd.Parameters.AddWithValue("@RFood", RFood);
        cmd.Parameters.AddWithValue("@UpdateTime", DateTime.Now.ToString());

        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
    }

    /// <summary>
    /// 更新親屬資料
    /// </summary>
    /// <returns></returns>
    public static void UpdateTable(string Rid, string Rname, string Rbirthday, string RFood)
    {

        SqlConnection cn = new SqlConnection(Common.DBConnectionString);

        SqlCommand cmd = new SqlCommand(
    "UPDATE [TravelRelative] SET [Rname] = @Rname, [Rbirthday] = @Rbirthday, [RFood] = @RFood, [UpdateTime] = @UpdateTime WHERE [Rid] = @Rid",
     cn);

        cmd.Parameters.AddWithValue("@Rname", Rname);
        cmd.Parameters.AddWithValue("@Rbirthday", Rbirthday);
        cmd.Parameters.AddWithValue("@RFood", RFood);
        cmd.Parameters.AddWithValue("@UpdateTime", DateTime.Now.ToString());
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
    }
    /// <summary>
    /// 刪除單一親屬資料(使用親屬的身分證字號Rid)
    /// </summary>
    /// <returns></returns>
    public static void DeleteTableByRid(string Rid)
    {
        SqlConnection cn = new SqlConnection(Common.DBConnectionString);
        SqlCommand cmd = new SqlCommand(
             "DELETE TravelRelative where Rid = @Rid",
             cn);
        cmd.Parameters.AddWithValue("@Rid", Rid);
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
    }

    /// <summary>
    /// 刪除親屬資料(使用員工的身分證字號EmployeeId，刪除該員工所有的報名親屬資料)
    /// </summary>
    /// <returns></returns>
    public static void DeleteTableByEmployeeId(string EmployeeId)
    {
        SqlConnection cn = new SqlConnection(Common.DBConnectionString);
        SqlCommand cmd = new SqlCommand(
             "DELETE TravelRelative where EmployeeId = @EmployeeId",
             cn);
        cmd.Parameters.AddWithValue("@EmployeeId", EmployeeId);
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
    }
}